Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Security.Cryptography


Public Class DBUtilities

    Public Const SQL_SCRIPT_PATH As String = "~\App_Data\ExecuteScript.sql"
    Public Const SQL_SCRIPT_PATH_RENAME As String = "~\App_Data\ExecuteScript.run"
    Public Const SQL_SCRIPT_NEW_DATABASE As String = "~/Scripts/CreateDatabase.sql"
    Public Const SQL_NEW_DATABASE_ADDRESS As String = "~/App_Data"
    Public Const DEFAULT_CONNECTION_STRING As String = "Data Source=.\TimeLive;Initial Catalog=TimeLive;User ID=sa;Password=1!Timelive"
    Public Const DEFAULT_DATABASE As String = "TimeLive"

    Public Shared Sub SetRowForInserting(ByVal e As System.Web.UI.WebControls.ObjectDataSourceMethodEventArgs)
        If Not System.Web.HttpContext.Current.Session("AccountEmployeeId") Is Nothing Then
            e.InputParameters("CreatedByEmployeeId") = System.Web.HttpContext.Current.Session("AccountEmployeeId")
            e.InputParameters("ModifiedByEmployeeId") = System.Web.HttpContext.Current.Session("AccountEmployeeId")
        Else
            e.InputParameters("CreatedByEmployeeId") = 1
            e.InputParameters("ModifiedByEmployeeId") = 1

        End If

    End Sub
    Public Shared Sub SetHardcodedSessionValues()
        System.Web.HttpContext.Current.Session.Add("AccountId", 232)
        System.Web.HttpContext.Current.Session.Add("AccountRoleId", 367)

    End Sub


    Public Shared Sub SetRowForUpdating(ByVal e As System.Web.UI.WebControls.ObjectDataSourceMethodEventArgs)

        e.InputParameters("ModifiedByEmployeeId") = DBUtilities.GetModifiedByEmployeeId

    End Sub

    Public Shared Function GetModifiedByEmployeeId() As Integer
        If Not System.Web.HttpContext.Current.Session("ModifiedByEmployeeId") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("ModifiedByEmployeeId")
        Else
            Return 1
        End If
    End Function
    Public Shared Function GetSessionAccountRoleId() As Integer
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return 0
        ElseIf Not System.Web.HttpContext.Current.Session("AccountRoleId") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("AccountRoleId")
        Else
            Return 39
        End If
    End Function

    Public Shared Function GetSessionAccountEmployeeId() As Integer
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return 0
        ElseIf Not System.Web.HttpContext.Current.Session("AccountEmployeeId") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("AccountEmployeeId")
        Else
            Return 39
        End If
    End Function

    Public Shared Function GetSessionAccountId() As Integer
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return 64
        ElseIf Not System.Web.HttpContext.Current.Session("AccountId") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("AccountId")
        Else
            Return 64
        End If
    End Function
    Public Shared Function GetShowClockStartEnd() As Boolean
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return True
        ElseIf Not System.Web.HttpContext.Current.Session("ShowClockStartEnd") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("ShowClockStartEnd")
        Else
            Return True
        End If
    End Function
    Public Shared Function GetTimeZoneId() As Byte
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return 6
        ElseIf Not System.Web.HttpContext.Current.Session("TimeZoneId") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("TimeZoneId")
        Else
            Return 6
        End If
    End Function
    Public Shared Function GetCurrencyId() As Integer
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return 1
        ElseIf Not System.Web.HttpContext.Current.Session("CountryId") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("CountryId")
        Else
            Return 1
        End If
    End Function
    Public Shared Function GetTimeEntryFormat() As Integer
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return 0
        ElseIf Not System.Web.HttpContext.Current.Session("TimeEntryFormat") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("TimeEntryFormat")
        Else
            Return 0
        End If
    End Function
    Public Shared Function GetAccountCountryId() As Byte
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return True
        ElseIf Not System.Web.HttpContext.Current.Session("CountryId") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("CountryId")
        Else
            Return 233
        End If
    End Function
    Public Shared Function GetDefaultCurrencyId() As Byte
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return True
        ElseIf Not System.Web.HttpContext.Current.Session("DefaultCurrencyId") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("DefaultCurrencyId")
        Else
            Return 1
        End If
    End Function
    Public Shared Function GetAccountBaseCurrencyId() As Integer
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return 1
        ElseIf Not System.Web.HttpContext.Current.Session("AccountBaseCurrencyId") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("AccountBaseCurrencyId")
        Else
            Return 1
        End If
    End Function

    Public Shared Function GetFromEmailAddressDisplayName() As String
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return "TimeReport"
        ElseIf Not System.Web.HttpContext.Current.Session("FromEmailAddressDisplayName") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("FromEmailAddressDisplayName")
        Else
            Return "TimeReport"
        End If
    End Function

    Public Shared Function GetTimeHorasMaximas() As String
        Dim h As String
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return "16:00"
        ElseIf Not System.Web.HttpContext.Current.Session("HourMaxDay") Is Nothing Then
            h = Format(System.Web.HttpContext.Current.Session("HourMaxDay"), "00") & ":00"
            h = IIf(h = ":00", "16:00", h)
            Return h
        Else
            Return "16:00"
        End If
    End Function
    Public Shared Function GetNumberOfBlankRowsInTimeEntry() As Short
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return "2"
        ElseIf Not System.Web.HttpContext.Current.Session("NumberOfBlankRowsInTimeEntry") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("NumberOfBlankRowsInTimeEntry")
        Else
            Return "2"
        End If
    End Function
    Public Shared Function GetFromEmailAddress() As String
        If Not System.Web.HttpContext.Current Is Nothing Then
            If System.Web.HttpContext.Current.Session Is Nothing Then
                Return "no-reply@pe.pwc.com"
            ElseIf Not System.Web.HttpContext.Current.Session("FromEmailAddress") Is Nothing Then
                Return System.Web.HttpContext.Current.Session("FromEmailAddress")
            Else
                Return "no-reply@pe.pwc.com"
            End If
        Else
            Return "no-reply@pe.pwc.com"
        End If
    End Function
    Public Shared Sub AfterInsert(ByVal objGridView As GridView)
        objGridView.DataBind()
    End Sub

    Public Shared Sub AfterUpdate(ByVal objGridView As GridView)
        objGridView.DataBind()
    End Sub

    Public Shared Function GetMinutesOfTime(ByVal TimeValue As DateTime) As Integer
        Return (TimeValue.Hour * 60) + TimeValue.Minute
    End Function




    
    Public Shared Function IsDefaultConnectionStringSetup() As Boolean
        If DBUtilities.GetConnectionString = DBUtilities.DEFAULT_CONNECTION_STRING Then
            Return True
        Else
            Return False
        End If
    End Function
    Public Shared Function IsDefaultDatabaseExist() As Boolean
        Dim sql As String = "SELECT count(name) FROM master.dbo.sysdatabases WHERE [name]=" & "'" & DBUtilities.DEFAULT_DATABASE & "'"
        If DBUtilities.ExecuteCommand(sql, DBUtilities.GetMasterDatabaseConnection, True) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
    Public Shared Function VerifyMasterDatabaseConnection() As Boolean
        Try
            DBUtilities.GetMasterDatabaseConnection()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Shared Function GetMasterDatabaseConnection(Optional ByVal DBConnection As SqlConnection = Nothing) As SqlConnection
        Dim Cn As New SqlConnection(DBUtilities.GetConnectionStringWithoutDatabaseName(DBConnection))
        Cn.Open()
        Return Cn
    End Function
    
    Public Shared Function IsDatabaseInstanceExist() As Boolean
        Try
            DBUtilities.GetMasterDatabaseConnection()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Shared Function VerifyTimeLiveConnection() As Boolean
        Try
            DBUtilities.GetTimeLiveDBConnection()
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Shared Sub CreateUserDatabase(ByVal DatabaseName As String, ByVal CN As SqlConnection)
        If DatabaseName <> "" Then
            Dim sql As String = "Create Database " & DatabaseName
            DBUtilities.ExecuteCommand(sql, DBUtilities.GetMasterDatabaseConnection(CN))
        End If
    End Sub
  
    Public Shared Sub CreateUserDatabaseLoginPassword(ByVal Login As String, ByVal Password As String, ByVal DBName As String, ByVal CN As SqlConnection)
        Dim sql1 As String = "Use [Master] IF Not EXISTS(SELECT * FROM syslogins WHERE name = '" & Login & "') begin "
        Dim sql As String = sql1 & "EXEC master.dbo.sp_addlogin @loginame = N'" & Login & "', @passwd = N'" & Password & "' End"
        DBUtilities.ExecuteCommand(sql, DBUtilities.GetMasterDatabaseConnection(CN))
    End Sub

    Public Shared Sub ChangeDefaultDatabaseForLogin(ByVal Login As String, ByVal DBName As String, ByVal CN As SqlConnection)
        Dim sql As String = " EXEC master.dbo.sp_defaultdb @loginame= N'" & Login & "', @defdb= N'" & DBName & "'"
        DBUtilities.ExecuteCommand(sql, CN)

    End Sub
    Public Shared Sub CreateDatabaseSecurityUser(ByVal Login As String, ByVal CN As SqlConnection)
        Dim sql As String = "EXEC dbo.sp_grantdbaccess @loginame = N'" & Login & "', @name_in_db = N'" & Login & "' EXEC sp_addrolemember N'db_owner', N'" & Login & "'"
        DBUtilities.ExecuteCommand(sql, CN)
    End Sub
    Public Shared Function GetConnectionString() As String
        Return System.Configuration.ConfigurationManager.ConnectionStrings("ClubConnectionString").ConnectionString
    End Function
    Public Shared Function GetUserConnectionString(ByVal CN As SqlConnection) As String
        Return CN.ConnectionString
    End Function
    Public Shared Function GetConnectionStringWithoutDatabaseName(Optional ByVal DBConnection As SqlConnection = Nothing) As String
        If Not DBConnection Is Nothing Then
            Dim conn As SqlConnection = DBConnection
            Return DBUtilities.GetUserConnectionString(conn).Replace("Initial Catalog=" & conn.Database.ToString & ";", "")
        Else
            Dim con As New SqlConnection(DBUtilities.GetConnectionString)
            Return DBUtilities.GetConnectionString().Replace("Initial Catalog=" & con.Database.ToString & ";", "")
        End If
    End Function

    Public Shared Function GetConnectionStringDatabaseName() As String
        Dim con As New SqlConnection(DBUtilities.GetConnectionString)
        Return con.Database.ToString
    End Function

    Public Shared Function GetConnectionStringDataSource() As String
        Dim con As New SqlConnection(DBUtilities.GetConnectionString)
        Return con.DataSource.ToString
    End Function


    Public Shared Function GetConnectionStringUserID() As String
        Return "Sa"
    End Function


    Public Shared Function GetConnectionStringPassWord() As String
        Return "587412302"
    End Function



    Public Shared Sub BackupDatabase(ByVal FolderName As String)
        Dim cn As New SqlConnection(DBUtilities.GetConnectionString)
        Dim sql As String = "BACKUP DATABASE [" & DBUtilities.GetConnectionStringDatabaseName & "] TO  DISK = '" & FolderName & "TimeLiveDBBackup.bak" & "' WITH FORMAT, INIT,  NAME = N'" & DBUtilities.GetConnectionStringDatabaseName & "-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10"
        DBUtilities.ExecuteCommand(sql, cn)
    End Sub
    Public Shared Sub CreateDatabaseFromBackupAndRestore(ByVal FolderName As String)
        Dim cn As New SqlConnection(DBUtilities.GetConnectionString)
        Dim sql As String = "Create Database [" & DBUtilities.GetSessionAccountId & "] ON  PRIMARY (NAME = '" & DBUtilities.GetSessionAccountId & "', FILENAME = '" & FolderName & DBUtilities.GetSessionAccountId & ".mdf')  LOG ON (NAME = '" & DBUtilities.GetSessionAccountId & ".log', FILENAME = '" & FolderName & DBUtilities.GetSessionAccountId & "_log.ldf')"
        DBUtilities.ExecuteCommand(sql, cn)
        Dim sql1 As String = "RESTORE DATABASE [" & DBUtilities.GetSessionAccountId & "] FROM  DISK = N'" & FolderName & "TimeLiveDBBackup.bak" & "' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10"
        DBUtilities.ExecuteCommand(sql1, cn)
    End Sub
    Public Shared Sub DeleteAllDataFromAnotherAccount()
        Dim cn As New SqlConnection(DBUtilities.GetConnectionString)
        Dim sql As String
        sql = "Use " & DBUtilities.GetSessionAccountId
        DBUtilities.ExecuteCommand(sql, cn)
    End Sub

    Public Shared Function ExecuteCommand(ByVal strSQL As String, Optional ByVal DBConnection As SqlConnection = Nothing, Optional ByVal IsExecuteScalar As Boolean = False) As Integer
        Dim objConnection As SqlConnection
        If DBConnection Is Nothing Then
            objConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("LiveConnectionString").ConnectionString)
            objConnection.Open()
        Else
            objConnection = DBConnection
            If objConnection.State = ConnectionState.Closed Then
                objConnection.Open()
            End If
        End If
        Dim sqlCommand As New SqlClient.SqlCommand
        sqlCommand.Connection = objConnection
        sqlCommand.CommandText = strSQL

        Dim recordsAffected As Integer

        If IsExecuteScalar = False Then
            recordsAffected = sqlCommand.ExecuteNonQuery()
        Else
            recordsAffected = sqlCommand.ExecuteScalar()
        End If

        objConnection.Close()
        Return recordsAffected

    End Function
    Public Shared Function GetTimeLiveDBConnection() As SqlConnection
        Dim objConnection As SqlConnection
        objConnection = New SqlConnection(DBUtilities.GetConnectionString)
        objConnection.Open()
        Return objConnection
    End Function

    Private Shared Function StrToByte(ByVal textdata As String) As Byte()
        Dim encoding As New System.Text.ASCIIEncoding()
        Return encoding.GetBytes(textdata)

    End Function

    Public Shared Function IsShowWorkTypeInTimeSheet() As Boolean

        Return System.Web.HttpContext.Current.Session("ShowWorkTypeInTimeSheet")

    End Function
    Public Shared Function IsShowCostCenterInTimeSheet() As Boolean

        Return System.Web.HttpContext.Current.Session("ShowCostCenterInTimeSheet")

    End Function

    Public Shared Function GetWeekStartDayInWeekTimeEntry() As Short
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return "1"
        ElseIf Not System.Web.HttpContext.Current.Session("WeekStartDay") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("WeekStartDay")
        Else
            Return "1"
        End If
    End Function

    Public Shared Function GetDefaultTimeEntryMode() As String
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return "Day View"
        ElseIf Not System.Web.HttpContext.Current.Session("DefaultTimeEntryMode") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("DefaultTimeEntryMode")
        Else
            Return "Day View"
        End If
    End Function
    Public Shared Function GetPageSize() As Integer
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return "10"
        ElseIf Not System.Web.HttpContext.Current.Session("PageSize") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("PageSize")
        Else
            Return "10"
        End If
    End Function


    Public Shared Function FichaIncripcionID() As Integer
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return 0
        ElseIf Not System.Web.HttpContext.Current.Session("FichaIncripcionID") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("FichaIncripcionID")
        Else
            Return 0
        End If
    End Function

    Public Shared Function FichaIncripcionID_E() As Integer
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return 0
        ElseIf Not System.Web.HttpContext.Current.Session("FichaIncripcionID_E") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("FichaIncripcionID_E")
        Else
            Return 0
        End If
    End Function
    Public Shared Function TitularID() As Integer
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return 10
        ElseIf Not System.Web.HttpContext.Current.Session("TitularID") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("TitularID")
        Else
            Return 10
        End If
    End Function

    Public Shared Function PersonaID() As Integer
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return 0
        ElseIf Not System.Web.HttpContext.Current.Session("PersonaID") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("PersonaID")
        Else
            Return 0
        End If
    End Function

    Public Shared Function GetRutaFoto() As String
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return ""
        ElseIf Not System.Web.HttpContext.Current.Session("RutaFoto") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("RutaFoto")
        Else
            Return ""
        End If
    End Function

    Public Shared Function Formulario() As String
        If System.Web.HttpContext.Current.Session Is Nothing Then
            Return 0
        ElseIf Not System.Web.HttpContext.Current.Session("Formulario") Is Nothing Then
            Return System.Web.HttpContext.Current.Session("Formulario")
        Else
            Return 0
        End If
    End Function

    ''Public Shared Function KEYFichaInscripcion() As Integer
    ''    Dim request As HttpWebRequest
    ''    If Request.QueryString("KEY").ToString() Is Nothing Then
    ''        Return 0
    ''    ElseIf Not System.Web.HttpContext.Current.Session("PersonaID") Is Nothing Then
    ''        Return System.Web.HttpContext.Current.Session("PersonaID")
    ''    Else
    ''        Return 0
    ''    End If
    ''End Function


End Class


